﻿using CuaHangLego.Bussiness;
using CuaHangLego.Bussiness.Parser;
using CuaHangLego.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CuaHangLego.DataAccess
{
    public class SanPhamKhoDA : DABase, IDA<SanPhamKho>
    {
        public static SanPhamKhoDA Instance = new SanPhamKhoDA();

        public int Insert(SanPhamKho item)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("INSERT INTO SANPHAM ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9}) VALUES(N'{10}',{11},{12},{13},'{14}',{15},{16},'{17}',{18}, {19}); SELECT CAST(scope_identity() AS int);",
                "Ten", "ChuDeID", "SoManhGhep", "GiaBan", "NgaySX", "BaoHanh", "SoLuong", "HinhAnh", "GiaMua", "IsVisible",
                item.Ten, item.ChuDe.MaSo, item.SoManhGhep, item.GiaBan, TConvert.CSDateTimeToSQL(item.NgaySX), item.BaoHanh, 0, item.HinhAnh, item.GiaMua, item.IsVisible ? 1 : 0);
            OpenDB();
            int rs = int.Parse(ExecuteQuerySingleValue(sql).ToString());
            CloseDB();
            if (rs != -1)
            {
                NhatKySanPhamDA.Instance.Insert(rs);
            }
            return rs;
        }
        public bool Update(SanPhamKho item)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("UPDATE SANPHAM SET {0}=N'{10}',{1}={11},{2}={12},{3}={13},{4}='{14}',{5}={15},{6}={16},{7}='{17}',{8}={18},{9}={19} WHERE ID={20};",
                "Ten", "ChuDeID", "SoManhGhep", "GiaBan", "NgaySX", "BaoHanh", "SoLuong", "HinhAnh", "GiaMua", "IsVisible",
                item.Ten, item.ChuDe.MaSo, item.SoManhGhep, item.GiaBan, TConvert.CSDateTimeToSQL(item.NgaySX), item.BaoHanh, item.SoLuong, item.HinhAnh, item.GiaMua, item.IsVisible ? 1 : 0, item.MaSo);
            OpenDB();
            int rs = ExecuteQuery(sql);
            CloseDB();
            return rs > 0;
        }

        public bool Delete(SanPhamKho item)
        {
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("UPDATE SANPHAM SET {0}={1} WHERE {2}={3}", "IsVisible", 0, "ID", item.MaSo);
            OpenDB();
            int rs = ExecuteQuery(sql);
            CloseDB();
            return rs > 0;
        }

        public IList<SanPhamKho> GetAll()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT * FROM SANPHAM");
            cmd.CommandType = System.Data.CommandType.Text;
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            IList<SanPhamKho> list = new List<SanPhamKho>();
            while (reader.Read())
            {
                list.Add(SanPhamKhoParser.Parse(reader));
            }
            CloseDB();
            return list;
        }

        public IList<SanPhamKho> GetAllWithoutDeletedItem()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT * FROM SANPHAM WHERE IsVisible=1");
            cmd.CommandType = System.Data.CommandType.Text;
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            IList<SanPhamKho> list = new List<SanPhamKho>();
            while (reader.Read())
            {
                list.Add(SanPhamKhoParser.Parse(reader));
            }
            CloseDB();
            return list;
        }

        public IList<SanPhamKho> GetByTheme(int ID)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT * FROM SANPHAM WHERE ChuDeID={0}", ID);
            cmd.CommandType = System.Data.CommandType.Text;
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            IList<SanPhamKho> list = new List<SanPhamKho>();
            while (reader.Read())
            {
                list.Add(SanPhamKhoParser.Parse(reader));
            }
            CloseDB();
            return list;
        }

        public SanPhamKho GetByID(int ID)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT * FROM SANPHAM WHERE {0}={1}", "ID", ID);
            cmd.CommandType = System.Data.CommandType.Text;
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            SanPhamKho sp = new SanPhamKho();
            while (reader.Read())
            {
                sp = SanPhamKhoParser.Parse(reader);
                break;
            }
            CloseDB();
            return sp;
        }

        public bool ChangeQuatity(int ID, int n)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("SELECT SoLuong FROM SANPHAM WHERE {0}={1}", "ID", ID);
            cmd.CommandType = System.Data.CommandType.Text;
            OpenDB();
            SqlDataReader reader = ExecuteQueryReader(cmd);
            int quantity = 0;
            while (reader.Read())
            {
                quantity = int.Parse(reader["SoLuong"].ToString());
                break;
            }
            CloseDB();
            OpenDB();
            SqlCommand sql = new SqlCommand();
            sql.CommandText = string.Format("UPDATE SANPHAM SET {0}={1} WHERE ID={2};",
                "SoLuong", quantity + n, ID);
            int rs = ExecuteQuery(sql);
            CloseDB();
            return rs > 0;
        }
    }
}
